﻿CREATE TABLE [dbo].[DocumentePlataDetaliu] (
    [IDDocPlataDetaliu] INT             IDENTITY (1, 1) NOT NULL,
    [IDDocument]        BIGINT          NULL,
    [IDDocumentPlata]   INT             NOT NULL,
    [Valoare]           DECIMAL (18, 2) CONSTRAINT [DF_DocumentePlataDetaliu_Valoare] DEFAULT ((0)) NOT NULL,
    [ValoareTVA]        DECIMAL (18, 2) DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_DocumentePlataDetaliu] PRIMARY KEY CLUSTERED ([IDDocPlataDetaliu] ASC),
    CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlata] FOREIGN KEY ([IDDocumentPlata]) REFERENCES [dbo].[DocumentePlata] ([IDDocumentPlata]),
    CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlataDetaliu] FOREIGN KEY ([IDDocument]) REFERENCES [dbo].[Documente] ([IDDocument])
);


GO


CREATE TRIGGER [dbo].[VerificaTotalDocument] 
   ON  [dbo].[DocumentePlataDetaliu]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @mesaj varchar(200)
	DECLARE @nrdoc varchar(200)
	DECLARE @SumaPlatita decimal(18,2), @sumaDatorata decimal(18,2)
	SET @nrdoc=''
	SET @SumaPlatita = 0
	SET @sumaDatorata = 0
	
	
	SELECT @nrdoc=MAX(d.NrDoc +  '/' +CONVERT(varchar(20),d.DataDoc,104)),  @SumaDatorata = MAX(ABS(d.Total)) ,
	  @SumaPlatita = MAX(ABS(pl.ValoarePlatita)) 
	FROM (SELECT dpd.IDDocument, SUM(ABS(dpd.Valoare)) ValoarePlatita, SUM(ABS(dpd.ValoareTVA)) ValoareTVAPlatita  FROM 
	DocumentePlataDetaliu dpd JOIN inserted i on i.IDDocument = dpd.IDDocument
	GROUP BY dpd.IDDocument) pl 
	JOIN Documente d ON pl.IDDocument = d.IDDocument  
	HAVING MAX(ABS(pl.ValoarePlatita))- MAX(ABS(d.Total)) >0  OR  
	MAX(ABS(pl.ValoareTVAPlatita)) - MAX(ABS(d.TotalValoareTVA)) >0
	
	IF @nrdoc<>''
		GOTO Error1
	
    GOTO Gata
	Error1:
	    SET @mesaj = 'Incasare/plata('+CAST(@SumaPlatita as varchar(25))+ ') mai mare decat totalul documentul incasat/platit ('+CAST(@sumaDatorata as varchar(25))+'): ' +@nrdoc
		RAISERROR (@mesaj, 16, 1)
		ROLLBACK TRANSACTION
	Gata:
END